<?
session_start();
include('../config.php');
include("../classes/connect.php");
require_once('../classes/class.jira.php');



if(isset($_GET['year']) AND isset($_GET['todate'])){
        $totalopencv = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_OpenCV."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
        
         $totalshortlist = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_ShortList."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totalinterviewed = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_Interviewed."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totalTested = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_Tested."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totalselected = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_Selected."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totalrejected = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_Rejected."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totaloffered = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_Offered."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totalinterviewcall = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_InterviewCalled."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totaltestcalled = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_TestCalled."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totaljoined = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_Joined."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totalfinalinterviewcalled = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_FinalInterviewCalled."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
         
         $totalfinalinterview = "SELECT COUNT(Distinct u.id) AS total,m.month
            FROM (
                    SELECT 'Jan' AS MONTH
                    UNION SELECT 'Feb' AS MONTH
                    UNION SELECT 'Mar' AS MONTH
                    UNION SELECT 'Apr' AS MONTH
                    UNION SELECT 'May' AS MONTH
                    UNION SELECT 'Jun' AS MONTH
                    UNION SELECT 'Jul' AS MONTH
                    UNION SELECT 'Aug' AS MONTH
                    UNION SELECT 'Sep' AS MONTH
                    UNION SELECT 'Oct' AS MONTH
                    UNION SELECT 'Nov' AS MONTH
                    UNION SELECT 'Dec' AS MONTH
                   ) AS m
                    LEFT JOIN trn_activity u ON MONTH(STR_TO_DATE(CONCAT(m.month,'".$_GET['year']."'),'%M %Y')) = MONTH(date(FROM_UNIXTIME(u.modifiedDate))) AND YEAR(date(FROM_UNIXTIME(u.modifiedDate))) = '".$_GET['year']."' AND u.status ='".$action_FinalInterviewed."'
                    GROUP BY m.month
                    ORDER BY FIELD( m.month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' )";
        
        $arr_opencv = array();
        $arr_rejected = array();
        $arr_selected = array();
        $arr_tested = array();
        $arr_shortlisted = array();
        $arr_offered = array();
        $arr_interviewcalled = array();
        $arr_testcalled = array();
        $arr_joined = array();
        $arr_finalinterview = array();
        $arr_finalinterviewcalled = array();
        $arr_interviewed= array();
        
        $queryOpencv=mysql_query($totalopencv);
        $queryrejected=mysql_query($totalrejected);
        $queryselected=mysql_query($totalselected);
        $querytested=mysql_query($totalTested);
        $queryshortlisted=mysql_query($totalshortlist);
        $queryoffered=mysql_query($totaloffered);
        $queryinterviewcalled=mysql_query($totalinterviewcall);
        $querytestcalled=mysql_query($totaltestcalled);
        $queryjoined=mysql_query($totaljoined);
        $queryfinalinterview=mysql_query($totalfinalinterview);
        $queryfinalinterviewcalled=mysql_query($totalfinalinterviewcalled);
        $queryinterviewed=mysql_query($totalinterviewed);
        
        
        
        
         while($rowint = mysql_fetch_array($queryinterviewed)){                
                $arr_interviewed[] = $rowint['total'];
                $i++;
        }
        
        while($rowfinalintcall = mysql_fetch_array($queryfinalinterviewcalled)){                
                $arr_finalinterviewcalled[] = $rowfinalintcall['total'];
                $i++;
        }
        
        while($rowfinalint = mysql_fetch_array($queryfinalinterview)){                
                $arr_finalinterview[] = $rowfinalint['total'];
                $i++;
        }
        
        
        while($rowjoin = mysql_fetch_array($queryjoined)){                
                $arr_joined[] = $rowjoin['total'];
                $i++;
        }
        
        
        
        while($rowtestcal = mysql_fetch_array($querytestcalled)){                
                $arr_testcalled[] = $rowtestcal['total'];
                $i++;
        }
        
        
        while($rowintercal = mysql_fetch_array($queryinterviewcalled)){                
                $arr_interviewcalled[] = $rowintercal['total'];
                $i++;
        }
        
        while($rowoffer = mysql_fetch_array($queryoffered)){                
                $arr_offered[] = $rowoffer['total'];
                $i++;
        }
        
        while($rowshort = mysql_fetch_array($queryshortlisted)){                
                $arr_shortlisted[] = $rowshort['total'];
                $i++;
        }
        
        while($rowtest = mysql_fetch_array($querytested)){                
                $arr_tested[] = $rowtest['total'];             
        }
        
        while($rowselect = mysql_fetch_array($queryselected)){                
                $arr_selected[] = $rowselect['total'];             
        }
        
        
        while($rowrejec = mysql_fetch_array($queryrejected)){                
                $arr_rejected[] = $rowrejec['total'];               
        }
         while($rowOpencv = mysql_fetch_array($queryOpencv)){                
                $arr_opencv[] = $rowOpencv['total'];                
        }
        
        
        $data = array();
        $data['Opencv']=$arr_opencv;
        $data['rejected']=$arr_rejected;
        $data['shortlist']=$arr_shortlisted;
        $data['tested']=$arr_tested;
        $data['testcall']=$arr_testcalled;
        $data['interview']=$arr_interviewed;
        $data['interviewcall']=$arr_interviewcalled;
        $data['finalinterview']=$arr_finalinterview;
        $data['finalinterviewcall']=$arr_finalinterviewcalled;
        $data['offer']=$arr_offered;
        $data['join']=$arr_joined;
        $data['selected']=$arr_selected;
        
     echo json_encode($data);
}

?>		
			
			
			
